<?php
require('functions/page_load.php');
require_once("../classes/class.phpmailer.php");

$home_nav = true;

// bans

if(isset($_GET['ban'])){

	$sql = "UPDATE users SET banned = 1 WHERE id = '".mysql_real_escape_string($_GET['ban'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	
		if(isset($_GET['job_id'])){
		$sql = "DELETE FROM jobs WHERE id = '".mysql_real_escape_string($_GET['job_id'])."'";
		$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
		$job_results = '<div class="success_result">Job deleted and user banned successfully.</div>';
		}
		
		if(isset($_GET['tradesman_id'])){
		$tradesman_results = '<div class="success_result">User banned successfully.</div>';
		}

}

// deletes

if(isset($_GET['delete_job'])){

	$sql = "UPDATE jobs SET deleted = 1 WHERE id = '".mysql_real_escape_string($_GET['delete_job'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$job_results = '<div class="success_result">Job deleted successfully.</div>';

}

if(isset($_GET['delete_tradesman'])){

	$sql = "SELECT user_id FROM tradesman WHERE id = '".mysql_real_escape_string($_GET['delete_tradesman'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rs=mysql_fetch_assoc($query);
	$user_id = $rs['user_id'];

	$sql = "DELETE FROM tradesman WHERE id = '".mysql_real_escape_string($_GET['delete_tradesman'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$sql = "DELETE FROM users WHERE id = '".$user_id."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$tradesman_results = '<div class="success_result">Tradesman deleted successfully.</div>';

}

if(isset($_GET['delete_link'])){

	$sql = "DELETE FROM directory WHERE id = '".mysql_real_escape_string($_GET['delete_link'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$links_results = '<div class="success_result">Link deleted successfully.</div>';

}

// approves

if(isset($_GET['id']) && $_GET['action'] == 'activate_job'){

$tradesmen = array();

// activate job

	$sql = "UPDATE jobs SET status = 'Open', job_active = '1' WHERE id = '".$_GET['id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

// find approved tradesman

	$sql = "SELECT jobs.custom, jobs.area_id, jobs.trade_id, areas.area, trades.trade, jobs.title, users.email, users.firstname, users.surname, users.approvals, addresses.latitude, addresses.longitude, jobs.description FROM jobs 
	LEFT JOIN areas ON jobs.area_id = areas.id 
	LEFT JOIN trades ON jobs.trade_id = trades.id 
	LEFT JOIN users ON jobs.user_id = users.id 
	LEFT JOIN addresses ON jobs.address_id = addresses.id 
	WHERE jobs.id = '".$_GET['id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$job_rs=mysql_fetch_assoc($query);

	$job_link = build_job_link($job_rs['trade'],$job_rs['area'],$_GET['id']);

if($job_rs['approvals'] == '1'){

	$mail = new PHPMailer();
	$mail->IsSMTP(); // telling the class to use SMTP
	$mail->Host = "localhost"; // SMTP server
	$mail->From = "mail@iwantatradesman.co.uk";
	$mail->FromName = "I Want A Tradesman";

	$mail->AddAddress($job_rs['email']);

	$mail->Subject = "Job notification";
	$mail->Body = "Dear ".$job_rs['firstname']." ".$job_rs['surname']."\r\n\r\nCongratulations! Your job is now activated and awaiting quotation from local tradesmen.\r\n\r\nJob title: ".ucfirst($job_rs['title'])."\r\n\r\nYou can view the job here: http://www.iwantatradesman.co.uk".$job_link."\r\n\r\nKind regards\r\n\r\nThe I Want A Tradesman Team\r\n\r\n\r\nThis email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.";

	$mail->Send();
}

	if($job_rs['custom'] == '1'){

	$sql = "SELECT tradesman.id, email FROM tradesman 
	LEFT JOIN job_to_tradesman ON tradesman.id = job_to_tradesman.tradesman_id 
	LEFT JOIN users ON tradesman.user_id = users.id 
	WHERE job_to_tradesman.job_id = '".$_GET['id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	while($rs=mysql_fetch_assoc($query)){

	$tradesmen[$rs['id']] = $rs['email'];

	}

	}

	else{
	
	// find tradesmen within radius
		//Change By Vyas Ishan 24 May 2013 Task2 added the status field condition
	$sql = "SELECT tradesman.id, email, ROUND(SQRT(POW((69.1 * (".$job_rs['latitude']." - latitude)), 2) + POW((53 * (".$job_rs['longitude']." - longitude)), 2)), 1) AS distance, radius FROM tradesman 
			LEFT JOIN addresses ON tradesman.address_id = addresses.id 
			LEFT JOIN tradesman_to_trade ON tradesman_to_trade.tradesman_id = tradesman.id 
			LEFT JOIN users ON tradesman.user_id = users.id 
			LEFT JOIN subscriptions ON tradesman.subscription_id = subscriptions.id 
			WHERE tradesman_to_trade.trade_id = '".$job_rs['trade_id']."' AND tradesman.active = '1'  AND subscriptions.status = '1' 
			GROUP BY tradesman.id 
			HAVING distance <= subscriptions.radius";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	while($rs=mysql_fetch_assoc($query)){
	$tradesmen[$rs['id']] = $rs['email'];
	}

	}

// send emails

	foreach($tradesmen AS $key => $value){

	$sql = "SELECT firstname, surname FROM tradesman 
	LEFT JOIN users ON tradesman.user_id = users.id 
	WHERE tradesman.id = '".$key."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$user_rs=mysql_fetch_assoc($query);

	$mail = new PHPMailer();
	$mail->IsSMTP(); // telling the class to use SMTP
	$mail->Host = "localhost"; // SMTP server
	$mail->From = "mail@iwantatradesman.co.uk";
	$mail->FromName = "I Want A Tradesman";

	$mail->AddAddress($value);

	$mail->Subject = "Job notification";
	$mail->Body = "Dear ".$user_rs['firstname']." ".$user_rs['surname']."\r\n\r\nYou have a new job available to quote!\r\n\r\nJob title: ".ucfirst($job_rs['title'])."\r\nArea: ".$job_rs['area']."\r\n\r\nYou can view the job here: http://www.iwantatradesman.co.uk".$job_link."\r\n\r\nKind regards\r\n\r\nThe I Want A Tradesman Team\r\n\r\n\r\nThis email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.";

	$mail->Send();

	}

// setup sms

	$mail = new PHPMailer();
	$mail->IsSMTP(); // telling the class to use SMTP
	$mail->Host = "localhost"; // SMTP server
	$mail->From = $settings['sms_email'];
	$mail->FromName = $settings['sms_email'];

	$i = 0;

	foreach($tradesmen AS $key => $value){

	// check for sms and notification

	$sql = "SELECT sms_remaining, mobile, mobile_verified, custom_quotes FROM SMS 
	LEFT JOIN tradesman ON SMS.tradesman_id = tradesman.id 
	LEFT JOIN users ON tradesman.user_id = users.id 
	WHERE tradesman.id = '".$key."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$sms_rs=mysql_fetch_assoc($query);

	if($sms_rs['sms_remaining'] > 0 && strlen($sms_rs['mobile']) > 0 && $sms_rs['mobile_verified'] == '1' && ($sms_rs['custom_quotes'] == '1' && $job_rs['custom'] == '1' || $job_rs['custom'] != '1')){

	$i++;

	$mail->AddAddress("44".substr($sms_rs['mobile'], 1)."@bulksms.co.uk");

// deduct sms

	$new_sms_remaining = $sms_rs['sms_remaining'] - 1;

	$sql = "UPDATE SMS SET sms_remaining = '".$new_sms_remaining."' WHERE tradesman_id = '".$key."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	}

	}

// SEND SMS

	if($i > 0){

	$mail->Subject = $settings['sms_password'];
	$mail->Body = "You have a new job available to quote on I Want A Tradesman. Go to your account now and provide a quotation.";

	$mail->Send();

	}
	
	// add to twitter
	require_once('twitteroauth/twitteroauth.php');

	/* Create a TwitterOauth object with consumer/user tokens. */
	$connection = new TwitterOAuth(CONSUMER_KEY, CONSUMER_SECRET, OAUTH_TOKEN, OAUTH_TOKEN_SECRET);

	/* statuses/update */
	$parameters = array('status' => ucfirst($job_rs['title']).' - '.$job_rs['area'].' - http://www.iwantatradesman.co.uk'.$job_link);
	$status = $connection->post('statuses/update', $parameters);

	// add to facebook
	post_to_facebook('New job! '.$job_rs['trade'].' wanted in '.$job_rs['area'], 'http://www.iwantatradesman.co.uk'.$job_link, $job_rs['description']);

$job_results = '<div class="success_result">Job approved successfully.</div>';
	
}

if($_GET['action'] == 'activate_tradesman' && isset($_GET['id'])){

// activate / deactivate

	$sql = "SELECT user_id, active FROM tradesman WHERE id = '".mysql_real_escape_string($_GET['id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rs=mysql_fetch_assoc($query);

	$new_active = ($rs['active'] == 1) ? '0' : '1';

	$sql = "UPDATE tradesman SET active = '".$new_active."' WHERE id = '".mysql_real_escape_string($_GET['id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

if($new_active == '1'){

	$sql = "SELECT firstname, surname, email FROM users WHERE id = '".$rs['user_id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$user_rs=mysql_fetch_assoc($query);

	$mail = new PHPMailer();
	$mail->IsSMTP(); // telling the class to use SMTP
	$mail->Host = "localhost"; // SMTP server
	$mail->From = "mail@iwantatradesman.co.uk";
	$mail->FromName = "I Want A Tradesman";

	$mail->AddAddress($user_rs['email']);

	$mail->Subject = "Account approved";
	$mail->Body = "Dear ".$user_rs['firstname']." ".$user_rs['surname']."\r\n\r\nCongratulations! Your account has been approved, you now have access to a new stream of work.\r\n\r\nSo login now, update your profile and start quoting for jobs in your area!\r\n\r\nKind regards\r\n\r\nThe I Want A Tradesman Team\r\n\r\n\r\nThis email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.";

	$mail->Send();

}

}

if($_GET['action'] == 'activate_link'){

	$sql = "UPDATE directory SET approved = 1 WHERE id = '".mysql_real_escape_string($_GET['id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	
	$sql = "SELECT company_name, email FROM directory WHERE id = '".mysql_real_escape_string($_GET['id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$user_rs=mysql_fetch_assoc($query);
	
	// send email
	
	$mail = new PHPMailer();
	$mail->IsSMTP(); // telling the class to use SMTP
	$mail->Host = "localhost"; // SMTP server
	$mail->From = "mail@iwantatradesman.co.uk";
	$mail->FromName = "I Want A Tradesman";

	$mail->AddAddress($user_rs['email']);

	$mail->Subject = "Business Directory Listing approved";
	$mail->Body = "Dear ".$user_rs['company_name']."\r\n\r\nCongratulations! Your listing has now been approved.\r\n\r\nYou can now find your business listed here: http://www.iwantatradesman.co.uk/directory\r\n\r\nKind regards\r\n\r\nThe I Want A Tradesman Team\r\n\r\n\r\nThis email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.";

	$mail->Send();
	
	$links_results = '<div class="success_result">Link approved successfully.</div>';

}

// stats

	$sql = "SELECT id FROM jobs";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$total_jobs = mysql_num_rows($query);

	$sql = "SELECT id FROM tradesman";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$total_tradesmen = mysql_num_rows($query);
	
	$sql = "SELECT id FROM users";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$total_users = mysql_num_rows($query);

	$sql = "SELECT id FROM users WHERE newsletter = 1";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$total_newsletters = mysql_num_rows($query);
	
	$sql = "SELECT id FROM links";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$total_links = mysql_num_rows($query);
	
require('includes/header.php');
?>

<div class="side_container">
<h2 class="container_header">Stats</h2>

<ul>
<li><strong>Jobs: </strong><?php echo number_format($total_jobs); ?></li>
<li><strong>Tradesmen: </strong><?php echo number_format($total_tradesmen); ?></li>
<li><strong>Users: </strong><?php echo number_format($total_users); ?></li>
<li><strong>Newsletter: </strong><?php echo number_format($total_newsletters); ?></li>
<li><strong>Links: </strong><?php echo number_format($total_links); ?></li>
</ul>

</div>

<div class="main_container_wrapper">

	<div class="main_container">
	<h2 class="container_header">Jobs awaiting moderation</h2>
	
	<a href="add_job.php" class="create_button">Create new job</a>
	
	<?php echo $job_results; ?>
	
	<?php
	
	$sql = "SELECT jobs.id, users.id AS user_id, firstname, surname, title, trade, area, job_active, date_added FROM jobs 
	LEFT JOIN trades ON jobs.trade_id = trades.id 
	LEFT JOIN areas ON jobs.area_id = areas.id 
	LEFT JOIN users ON jobs.user_id = users.id 
	WHERE job_active = 0 AND deleted = 0 
	ORDER BY date_added DESC";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rows = mysql_num_rows($query);
	
	if($rows > 0){
	
	echo '<table width="100%" cellpadding="0" cellspacing="0" class="cms_table paginate_table_small">
	<thead><tr><th align="left">Title</th><th>User</th><th>Date</th><th width="50">Approve</th><th width="50">Ban</th><th width="50">Delete</th></tr></thead>
	<tbody>';
	
		while($rs=mysql_fetch_assoc($query)){

		echo '<tr><td align="left"><a href="edit_job.php?id='.$rs['id'].'">'.$rs['title'].'</a></td><td><a href="edit_user.php?id='.$rs['user_id'].'">'.$rs['firstname'].' '.$rs['surname'].'</a></td><td>'.date("d/m/Y", strtotime($rs['date_added'])).'</td><td><a href="?id='.$rs['id'].'&amp;action=activate_job">Approve</a></td><td><a onclick="confirm_ban(\'?ban='.$rs['user_id'].'&amp;job_id='.$rs['id'].'\',\'this user\');" title="Ban"><img src="images/na.png" alt="Ban" /></a></td><td><a onclick="confirm_delete(\'?delete_job='.$rs['id'].'\',\'this job\');" title="Delete"><img src="images/delete.png" alt="Delete" /></a></td></tr>';
		
		}
		
	echo '</tbody></table>';
	
	}
	
	else{
	
	echo '<p style="float: left; width: 100%; text-align: center;">There are currently no jobs awaiting moderation.</p>';
	
	}
	
	
	?>

	</div>


	<div class="main_container">
	<h2 class="container_header">Tradesmen awaiting moderation</h2>
	
	<a href="add_user.php" class="create_button">Create new tradesman</a>
	
	<?php echo $tradesman_results; ?>
	
	<?php
	
	$sql = "SELECT tradesman.id, company_name, member_since, user_id FROM tradesman 
			LEFT JOIN users ON tradesman.user_id = users.id 
			WHERE active = 0 AND deleted = 0 
			ORDER BY member_since DESC";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rows = mysql_num_rows($query);
	
	if($rows > 0){
	
	echo '	<table width="100%" cellpadding="0" cellspacing="0" class="cms_table paginate_table_small">
	<thead><tr><th align="left">Company name</th><th>Date</th><th width="50">Approve</th><th width="50">Ban</th><th width="50">Delete</th></tr></thead>
	<tbody>';
	
		while($rs=mysql_fetch_assoc($query)){

		echo '<tr><td align="left"><a href="edit_tradesman.php?id='.$rs['id'].'">'.$rs['company_name'].'</a></td><td>'.date("d/m/Y", strtotime($rs['member_since'])).'</td><td><a href="?id='.$rs['id'].'&amp;action=activate_tradesman">Approve</a></td><td><a onclick="confirm_ban(\'?ban='.$rs['user_id'].'&amp;tradesman_id='.$rs['id'].'\',\'this user\');" title="Ban"><img src="images/na.png" alt="Ban" /></a></td><td><a onclick="confirm_delete(\'?delete_tradesman='.$rs['id'].'\',\'this tradesman\');" title="Delete"><img src="images/delete.png" alt="Delete" /></a></td></tr>';
		
		}
		
	echo '</tbody></table>';
	
	}
	
	else{
	
	echo '<p style="float: left; width: 100%; text-align: center;">There are currently no tradesmen awaiting moderation.</p>';
	
	}
	
	?>
	

	</div>

	<div class="main_container">
	<h2 class="container_header">Links awaiting moderation</h2>
	
	<a href="add_directory.php" class="create_button">Create new link</a>
	
	<?php echo $links_results; ?>
	
	<?php
	
	$sql = "SELECT id, company_name, website, linkback, nolinkback FROM directory
			WHERE approved != 1 
			ORDER BY id DESC";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rows = mysql_num_rows($query);
	
	if($rows > 0){
	
	echo '	<table width="100%" cellpadding="0" cellspacing="0" class="cms_table paginate_table_small">
	<thead><tr><th align="left">Company</th><th>View website</th><th width="50">Linkback</th><th width="50">Approve</th><th width="50">Delete</th></tr></thead>
	<tbody>';
	
		while($rs=mysql_fetch_assoc($query)){
		
		$linkback = ($rs['linkback'] == '1' || $rs['nolinkback'] == '1') ? '<img src="images/yes.png" alt="Yes" />' : '<img src="images/no.png" alt="No" />' ;

		echo '<tr><td align="left"><a href="edit_directory.php?id='.$rs['id'].'">'.$rs['company_name'].'</a></td><td><a href="'.$rs['website'].'" target="_blank"><img src="images/preview.png" alt="" /></a></td><td>'.$linkback.'</td><td><a href="?id='.$rs['id'].'&amp;action=activate_link">Approve</a></td><td><a onclick="confirm_delete(\'?delete_link='.$rs['id'].'\',\'this link\');" title="Delete"><img src="images/delete.png" alt="Delete" /></a></td></tr>';
		
		}
		
	echo '</tbody></table>';
	
	}
	
	else{
	
	echo '<p style="float: left; width: 100%; text-align: center;">There are currently no links awaiting moderation.</p>';
	
	}
	
	?>

	</div>

</div>
	
<?php
	require('includes/footer.php');
?>